﻿using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using Dapper;
using MySql.Data.MySqlClient;

namespace ConsoleAppFramework.Util
{
    /// <summary>
    /// ADB是阿里的MySql的加强版
    /// </summary>
    public class DapperHelper_ADB : DapperHelperBase
    {
        public DapperHelper_ADB(string connectionString, int? commandTimeout = null)
        {
            ConnectionString = connectionString;
            CommandTimeout = commandTimeout;
        }
        protected override IDbConnection OpenConnection()
        {
            var conn = new MySqlConnection(ConnectionString);
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            return conn;
        }

        #region 使用LOAD DATA导入本地数据

        /// <summary>
        /// 使用LOAD DATA导入本地数据
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="tableName">表名</param>
        /// <param name="columns">字段</param>
        /// <returns></returns>
        public bool ExecuteCommand(string filePath, string tableName, string columns)
        {
            using (IDbConnection conn = OpenConnection())
            {
                string sqlText = $"load data local infile '{filePath.Replace("\\", "\\\\")}' replace into table {tableName} FIELDS TERMINATED by '\\t' ENCLOSED BY '\"' ESCAPED BY '\\\\'  LINES TERMINATED BY '\\r\\n' ({columns})";
                IDbCommand cmd = conn.CreateCommand();
                try
                {
                    cmd.CommandText = sqlText;
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    return false;
                }
            }
        }

        #endregion

        #region 执行多条SQL语句，实现数据库事务

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="sqlStringList">多条SQL语句</param>        
        public int ExecuteSqlTran(ConcurrentBag<String> sqlStringList)
        {
            using (IDbConnection conn = OpenConnection())
            {
                IDbTransaction tx = conn.BeginTransaction();
                IDbCommand cmd = conn.CreateCommand();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    foreach (var strSql in sqlStringList.Where(strSql => strSql.Trim().Length > 1))
                    {
                        cmd.CommandText = strSql;
                        count += cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                    return count;
                }
                catch (Exception ex)
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }

        #endregion

        #region 分页查询1
        /// <summary>
        /// 数据分页查询  --支持多表查询  
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="sqlWhere"></param>
        /// <param name="orderBy"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageNo"></param>
        /// <param name="fields"></param>
        /// <param name="parameters"></param>
        /// <returns> table1=总记录数，table2=分页数据  </returns>
        protected DataSet GetDataPageBase(string TableName, string sqlWhere, string orderBy, int pageSize = 10, int pageNo = 1, string fields = "*", object parameters = null)
        {
            DataSet ds = new DataSet();
            if (!string.IsNullOrWhiteSpace(sqlWhere))
            {
                sqlWhere = $" where {ClearAndOr(sqlWhere)}";
            }
            if (!string.IsNullOrWhiteSpace(orderBy))
            {
                orderBy = $" order by {orderBy}";
            }
            TableName = string.IsNullOrWhiteSpace(TableName) ? "table_01" : TableName;

            int begin_index = (pageNo - 1) * pageSize;
            string count_sql = $"select count(*) from {TableName} {sqlWhere} ;";
            string dt_sql = $"select {fields} from {TableName} {sqlWhere} {orderBy} LIMIT {begin_index},{pageSize} ;";
            //string sql = $@"
            //    {count_sql}
            //    {dt_sql}
            //";
            using (IDbConnection conn = OpenConnection())
            {
                using (var reader = conn.ExecuteReader(count_sql, parameters, commandType: CommandType))
                {
                    while (!reader.IsClosed)
                    {
                        ds.Tables.Add().Load(reader);
                    }
                }

                using (var reader = conn.ExecuteReader(dt_sql, parameters, commandType: CommandType))
                {
                    while (!reader.IsClosed)
                    {
                        ds.Tables.Add().Load(reader);
                    }
                }
            }

            return ds;
        }

        /// <summary>
        /// 数据分页查询  --支持多表查询  
        /// </summary>
        /// <param name="TableName">查询的表名</param>
        /// <param name="sqlWhere">sql 查询命令，不要含 where 关键字</param>
        /// <param name="orderBy">排序命令，不要含 order by 关键字</param>
        /// <param name="totalCount">总条数</param>
        /// <param name="fields">字段，默认为*</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="pageNo">页号</param>
        /// <returns></returns>
        public virtual DataTable GetDataPage(string TableName, string sqlWhere, string orderBy, out int totalCount, int pageSize = 10, int pageNo = 1, string fields = "*", object parameters = null)
        {
            DataTable dt = null;
            totalCount = 0;
            DataSet ds = GetDataPageBase(TableName, sqlWhere, orderBy, pageSize, pageNo, fields, parameters);
            if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
            {
                totalCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                dt = ds.Tables[1];
            }
            return dt;
        }

        #endregion

        #region 分页查询2

        /// <summary>
        /// dapper通用分页方法
        /// </summary>
        /// <typeparam name="T">泛型集合实体类</typeparam>
        /// <param name="fields">列</param>
        /// <param name="TableName">表</param>
        /// <param name="sqlWhere">条件</param>
        /// <param name="orderBy">排序</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">当前页显示条数</param>
        /// <param name="totalCount">结果集总数</param>
        /// <returns></returns>
        public IEnumerable<T> GetPageList<T>(string TableName, string sqlWhere, string orderBy, out long totalCount, int pageIndex = 1, int pageSize = 10, string fields = "*", object parameters = null)
        {
            int begin_index = (pageIndex - 1) * pageSize;
            string count_sql = $"select count(*) from {TableName} {sqlWhere} ;";
            string dt_sql = $"select {fields} from {TableName} {sqlWhere} {orderBy} LIMIT {begin_index},{pageSize} ;";
            string sql = $@"
                {count_sql}
                {dt_sql}
            ";

            using (IDbConnection conn = OpenConnection())
            {
                totalCount = conn.ExecuteScalar<long>(count_sql);
                return conn.Query<T>(dt_sql);
            }
        }

        #endregion

        #region 辅助方法

        /// <summary>
        /// 清除WHERE开头的 AND 和 OR
        /// </summary>
        /// <param name="where">待处理的where子句（不含where ' AND' or ' OR'开头）</param>
        /// <returns></returns>
        private string ClearAndOr(string where) => Regex.Replace(where.TrimStart(), @"^(AND|OR)", "", RegexOptions.IgnoreCase);
        #endregion
    }
}
